import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 本类的getRawData方法可以从天钿Daily获取原始数据，并经过筛选之后，保存在本地数据库中。</p>
 * 在实际使用过程中，应当
 * 1.先调用本类的getRawData方法；★
 * 2.人工插入、删除某些项目；★
 * 3.使用GetStatisticsData类进行数据分析；
 * 4.使用WriteReport类撰写统计月报。
 */
public class GetRawData {
    public static void main(String[] args) {
        getRawData(2022, 1);
        //StringBuilder stringBuilder = new StringBuilder();
        //getDeltaViewOfMembers(stringBuilder, 2021, 4);
        //System.out.println(stringBuilder.toString());
        //insertVideo(2020, 1, 78978783, "苍穹");
        //deleteVideo(763888102);
    }

    /**
     * 输入年份和月份，获得下个月的起始时间点
     *
     * @param year  年份
     * @param month 月份
     * @return 起始时间点，即本月1日4时整
     * @throws ParseException 转换错误
     */
    public static int getNextMonthTime(int year, int month) throws ParseException {
        if (month < 12) {
            return getThisMonthTime(year, month + 1);
        } else {
            return getThisMonthTime(year + 1, 1);
        }
    }

    /**
     * 输入年份和月份，获得这个月的起始时间点
     *
     * @param year  年份
     * @param month 月份
     * @return 起始时间点，即本月1日4时整
     * @throws ParseException 转换错误
     */
    public static int getThisMonthTime(int year, int month) throws ParseException {
        DateFormat dateFormat = new SimpleDateFormat("yyyy-M-dd HH:mm:ss");
        String string = year + "-" + month + "-01 04:00:00";
        long time = dateFormat.parse(string).getTime();
        return (int) (time / 1000L);
    }

    /**
     * 将月份转化为文本，例如2021年5月转化为“2021-05”
     *
     * @param year  年份
     * @param month 月份
     * @return 转化结果
     */

    public static String monthToString(int year, int month) {
        if (month < 10) {
            return year + "-0" + month;
        } else {
            return year + "-" + month;
        }
    }

    /**
     * 获取数据
     *
     * @param year  年份
     * @param month 月份
     */
    public static void getRawData(int year, int month) {
        try {
            // 要求投稿的pubdate大于等于pubdateLimit
            int pubdateLimit = getThisMonthTime(year, month);
            // 要求记录的added大于等于addedLimit，且小于等于addedLimit + 1小时
            int addedLimit = getNextMonthTime(year, month);
            String monthString = monthToString(year, month);

            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection tddConnection = DriverManager.
                    getConnection(TddAccount.URL, TddAccount.USER, TddAccount.PASSWORD);
            Connection localConnection = DriverManager.
                    getConnection(LocalAccount.URL, LocalAccount.USER, LocalAccount.PASSWORD);

            String querySql = "SELECT*FROM ( SELECT`aid`, MAX(`view`) AS`view`, MAX(`favorite`) AS`favorite`, MAX(`danmaku`) AS`danmaku`, MAX(`reply`) AS`reply`, MAX(`coin`) AS`coin`, MAX(`like`) AS`like`FROM tdd_video_record WHERE`added`> ? AND`added`< ? GROUP BY`aid`) AS t1, ( SELECT`aid`,`title`,`tags`,`desc`,`mid`,`pubdate` FROM tdd_video WHERE`pubdate`> ? ) AS t2 WHERE t1.`aid`= t2.`aid` ORDER BY `view` DESC;";
            PreparedStatement queryPrSt = tddConnection.prepareStatement(querySql);
            queryPrSt.setInt(1, addedLimit);
            queryPrSt.setInt(2, addedLimit + 3600);
            queryPrSt.setInt(3, pubdateLimit);

            System.out.println("【正常】现在开始尝试获取数据");
            queryPrSt.executeQuery();
            ResultSet resultSet = queryPrSt.getResultSet();
            System.out.println("【正常】获取数据结束");

            // 插入到table_video表中
            String insertSql1 = "INSERT INTO table_video (month, aid, pubdate, title, tags, `desc`, mid, `view`, favorite, danmaku, reply, coin, `like`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?);";
            PreparedStatement insertPrSt1 = localConnection.prepareStatement(insertSql1);
            // 插入到table_video_vocal表中
            String insertSql2 = "INSERT INTO table_video_vocal (aid, vid) VALUES (?,?);";
            PreparedStatement insertPrSt2 = localConnection.prepareStatement(insertSql2);

            int[] viewSum = {0, 0, 0, 0, 0};    // VU区，V，SV，Vsinger，Medium5
            int[] count = {0, 0, 0, 0, 0};      // VU区，V，SV，Vsinger，Medium5

            System.out.println("【正常】开始保存到本地数据库");

            while (resultSet.next()) {
                int aid = resultSet.getInt("aid");
                int view = resultSet.getInt("view");
                try {
                    insertLocalDB(aid, monthString, resultSet, insertPrSt1, insertPrSt2, "");
                } catch (SQLIntegrityConstraintViolationException exception) {
                    System.out.println("【异常】发生重复插入异常。" + exception.getMessage());
                }
                viewSum[0] += view;
                count[0]++;
            }

            System.out.println("【正常】保存到本地数据库结束");
            System.out.println(year + "年" + month + "月VOCALOID·UTAU区新曲投稿数" + count[0] + "，新曲播放量" + viewSum[0]);

            resultSet.close();
            queryPrSt.close();
            insertPrSt1.close();
            insertPrSt2.close();
            tddConnection.close();
        } catch (ParseException | SQLException | ClassNotFoundException exception) {
            exception.printStackTrace();
        }
    }

    /**
     * 删除误收的歌曲
     *
     * @param aid 误收的歌曲AV号
     */
    public static void deleteVideo(int aid) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection localConnection = DriverManager.
                    getConnection(LocalAccount.URL, LocalAccount.USER, LocalAccount.PASSWORD);
            PreparedStatement pDelete1 = localConnection.prepareStatement("DELETE FROM table_video_vocal WHERE aid=?;");
            pDelete1.setInt(1, aid);
            pDelete1.executeUpdate();
            PreparedStatement pDelete2 = localConnection.prepareStatement("DELETE FROM table_video WHERE aid=?;");
            pDelete2.setInt(1, aid);
            pDelete2.executeUpdate();
            System.out.println("已经移除AV" + aid);
        } catch (SQLException | ClassNotFoundException exception) {
            exception.printStackTrace();
        }
    }

    /**
     * 向某年某月的统计月报原始数据插入漏收的歌曲
     *
     * @param year      年份
     * @param month     月份
     * @param aid       AV号
     * @param vocalInfo 歌姬
     */
    public static void insertVideo(int year, int month, int aid, String vocalInfo) {
        try {
            // 要求投稿的pubdate大于等于pubdateLimit
            int pubdateLimit = getThisMonthTime(year, month);
            // 要求记录的added大于等于addedLimit，且小于等于addedLimit + 1小时
            int addedLimit = getNextMonthTime(year, month);
            String monthString = monthToString(year, month);

            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection tddConnection = DriverManager.
                    getConnection(TddAccount.URL, TddAccount.USER, TddAccount.PASSWORD);
            Connection localConnection = DriverManager.
                    getConnection(LocalAccount.URL, LocalAccount.USER, LocalAccount.PASSWORD);

            String querySql = "SELECT*FROM ( SELECT`aid`, MAX(`view`) AS`view`, MAX(`favorite`) AS`favorite`, MAX(`danmaku`) AS`danmaku`, MAX(`reply`) AS`reply`, MAX(`coin`) AS`coin`, MAX(`like`) AS`like`FROM tdd_video_record WHERE`aid`= ? AND `added`> ? AND`added`< ? GROUP BY`aid`) AS t1, ( SELECT`aid`,`title`,`tags`,`desc`,`mid`,`pubdate` FROM tdd_video WHERE`pubdate`> ? ) AS t2 WHERE t1.`aid`= t2.`aid` ORDER BY `view` DESC;";
            PreparedStatement queryPrSt = tddConnection.prepareStatement(querySql);
            queryPrSt.setInt(1, aid);
            queryPrSt.setInt(2, addedLimit);
            queryPrSt.setInt(3, addedLimit + 3600);
            queryPrSt.setInt(4, pubdateLimit);
            System.out.println("【正常】现在开始尝试获取数据");
            queryPrSt.executeQuery();
            ResultSet resultSet = queryPrSt.getResultSet();
            System.out.println("【正常】获取数据结束");

            // 插入到table_video表中
            String insertSql1 = "INSERT INTO table_video (month, aid, pubdate, title, tags, `desc`, mid, `view`, favorite, danmaku, reply, coin, `like`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?);";
            PreparedStatement insertPrSt1 = localConnection.prepareStatement(insertSql1);
            // 插入到table_video_vocal表中
            String insertSql2 = "INSERT INTO table_video_vocal (aid, vid) VALUES (?,?);";
            PreparedStatement insertPrSt2 = localConnection.prepareStatement(insertSql2);

            boolean flag = false; // 是否插入成功
            while (resultSet.next()) {
                try {
                    flag = true;
                    insertLocalDB(aid, monthString, resultSet, insertPrSt1, insertPrSt2, vocalInfo);
                } catch (SQLIntegrityConstraintViolationException exception) {
                    System.out.println("【异常】发生重复插入异常。" + exception.getMessage());
                }
            }
            if (!flag) {
                System.out.println("【错误】在时间段内找不到目标视频AV" + aid);
            } else {
                System.out.println("【正常】补充额外视频完毕");
            }

        } catch (ParseException | SQLException | ClassNotFoundException exception) {
            exception.printStackTrace();
        }
    }

    /**
     * 插入到本地数据库
     *
     * @param aid            AV号
     * @param monthString    月份
     * @param resultSet      数据
     * @param insertPrSt1    插入第一个表格的预编译
     * @param insertPrSt2    插入第二个表格的预编译
     * @param extraVocalInfo 额外的歌姬名称 使用String 默认时不填
     * @throws SQLException 可能会抛出SQL异常
     */
    private static void insertLocalDB(int aid, String monthString, ResultSet resultSet, PreparedStatement insertPrSt1, PreparedStatement insertPrSt2, String extraVocalInfo) throws SQLException {
        String title = resultSet.getString("title");
        String desc = resultSet.getString("desc");
        String tags = resultSet.getString("tags");
        FilterResult filterResult = Filter.check(extraVocalInfo + title, tags, desc);
        if (filterResult.isIncluded) {
            // 插入到table_video表中
            insertPrSt1.setString(1, monthString);
            insertPrSt1.setInt(2, aid);
            insertPrSt1.setInt(3, resultSet.getInt("pubdate"));
            insertPrSt1.setString(4, title);
            insertPrSt1.setString(5, tags);
            insertPrSt1.setString(6, desc);
            insertPrSt1.setInt(7, resultSet.getInt("mid"));
            insertPrSt1.setInt(8, resultSet.getInt("view"));
            insertPrSt1.setInt(9, resultSet.getInt("favorite"));
            insertPrSt1.setInt(10, resultSet.getInt("danmaku"));
            insertPrSt1.setInt(11, resultSet.getInt("reply"));
            insertPrSt1.setInt(12, resultSet.getInt("coin"));
            insertPrSt1.setInt(13, resultSet.getInt("like"));
            insertPrSt1.executeUpdate();
            // 插入到table_video_vocal表中
            for (int i = 0; i < Filter.VOCALS.length; i++) {
                if (filterResult.vocals[i]) {
                    insertPrSt2.setInt(1, aid);
                    insertPrSt2.setInt(2, i);
                    insertPrSt2.executeUpdate();
                }
            }
        }
    }

    public static String getCurrentDiantang(int year, int month) {
        try {
            // 要求投稿的pubdate大于等于pubdateLimit
            int pubdateLimit = getThisMonthTime(year, month);
            // 要求记录的added大于等于addedLimit，且小于等于addedLimit + 1小时
            int addedLimit = getNextMonthTime(year, month);
            String monthString = monthToString(year, month);

            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection tddConnection = DriverManager.
                    getConnection(TddAccount.URL, TddAccount.USER, TddAccount.PASSWORD);
            String querySql = "SELECT*FROM ( SELECT`aid`, MAX(`view`) AS`view`, MAX(`favorite`) AS`favorite`, MAX(`danmaku`) AS`danmaku`, MAX(`reply`) AS`reply`, MAX(`coin`) AS`coin`, MAX(`like`) AS`like`FROM tdd_video_record WHERE `view`>100000 AND `added`> ? AND`added`< ? GROUP BY`aid`) AS t1, ( SELECT`aid`,`title`,`tags`,`desc`,`mid`,`pubdate` FROM tdd_video WHERE`pubdate`> ? ) AS t2 WHERE t1.`aid`= t2.`aid` ORDER BY `view` DESC;";

            PreparedStatement queryPrSt = tddConnection.prepareStatement(querySql);
            queryPrSt.setInt(1, addedLimit);
            queryPrSt.setInt(2, addedLimit + 3600);
            queryPrSt.setInt(3, pubdateLimit);
            queryPrSt.executeQuery();
            ResultSet resultSet = queryPrSt.getResultSet();
            StringBuilder stringBuilder = new StringBuilder();
            while (resultSet.next()) {
                stringBuilder.append(resultSet.getString("aid")).append("\n");
            }
            resultSet.close();
            queryPrSt.close();
            tddConnection.close();
            return stringBuilder.toString();

        } catch (ParseException | SQLException | ClassNotFoundException exception) {
            exception.printStackTrace();
        }
        return "获取本月殿堂曲失败！";
    }

    /**
     * 获取各个UP主的播放量增幅情况
     *
     * @param stringBuilder 输出结果保存在StringBuilder里
     * @param year          年份
     * @param month         月份
     */
    public static void getDeltaViewOfMembers(StringBuilder stringBuilder, int year, int month) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection tddConnection = DriverManager.
                    getConnection(TddAccount.URL, TddAccount.USER, TddAccount.PASSWORD);
            String sql = "SELECT tdd_member.`name`, SUM(t1.`view`) AS `v` FROM tdd_member, ( SELECT tdd_video_record.`aid`,`title`,`mid`, MAX(`view`) AS`view`FROM tdd_video_record, tdd_video WHERE tdd_video_record.`added`> ? AND tdd_video_record.`added`< ? AND tdd_video_record.`aid`= tdd_video.`aid`GROUP BY`aid`) AS t1 WHERE t1.`mid`= tdd_member.`mid`GROUP BY tdd_member.`name`HAVING SUM(t1.`view`) > 5000000 ORDER BY SUM(t1.`view`) DESC LIMIT 200;";
            PreparedStatement preparedStatement = tddConnection.prepareStatement(sql);
            int time1 = getThisMonthTime(year, month);
            int time2 = getNextMonthTime(year, month);

            /* 查询月初各UP主播放量 */
            preparedStatement.setInt(1, time1 - 86400);
            preparedStatement.setInt(2, time1);
            preparedStatement.executeQuery();
            ResultSet resultSet1 = preparedStatement.getResultSet();
            Map<String, MembersView> startViewMap = new HashMap<>();
            while (resultSet1.next()) {
                String name = resultSet1.getString("name");
                MembersView membersView = new MembersView(name, resultSet1.getInt("v"));
                startViewMap.put(name, membersView);
            }
            resultSet1.close();

            /* 查询月末各UP主播放量 */
            preparedStatement.setInt(1, time2 - 86400);
            preparedStatement.setInt(2, time2);
            preparedStatement.executeQuery();
            ResultSet resultSet2 = preparedStatement.getResultSet();
            while (resultSet2.next()) {
                String name = resultSet2.getString("name");
                if (startViewMap.containsKey(name)) {
                    startViewMap.get(name).deltaView(resultSet2.getInt("v"));
                }
            }
            List<MembersView> list = new ArrayList<>(startViewMap.values());
            Collections.sort(list);
            for (int i = 0; i < 10; i++) {
                list.get(i).addToStringBuilder(stringBuilder);
            }

            resultSet2.close();
            preparedStatement.close();
            tddConnection.close();
        } catch (ParseException | SQLException | ClassNotFoundException exception) {
            exception.printStackTrace();
        }
    }

    public static void getDeltaFansOfMembers(StringBuilder stringBuilder, int year, int month) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection tddConnection = DriverManager.
                    getConnection(TddAccount.URL, TddAccount.USER, TddAccount.PASSWORD);
            String sql = "SELECT`follower`FROM tdd_member_follower_record WHERE`added`> ? AND`added`< ? AND`mid`= ?;";
            PreparedStatement preparedStatement = tddConnection.prepareStatement(sql);
            int time1 = getThisMonthTime(year, month);
            int time2 = getNextMonthTime(year, month);

            int[] mids = {36081646, 406948276, 406948651, 15817819, 623328493, 188839285, 3379951, 326258472, 43855, 341368, 123372, 893950};
            String[] names = {"洛天依", "言和", "乐正绫", "星尘Official", "五维介质Official", "心华Official", "ilem", "忘川风华录", "litterzy", "JUSF周存", "純白", "纳兰寻风"};
            int[] startFollowers = new int[mids.length];
            int[] endFollowers = new int[mids.length];

            System.out.println("time1" + time1);
            System.out.println("time2" + time2);

            getFollowersByMids(preparedStatement, time1, mids, startFollowers);
            getFollowersByMids(preparedStatement, time2, mids, endFollowers);

            for (int i = 0; i < mids.length; i++) {
                stringBuilder.append(names[i]).append("（涨粉")
                        .append(WriteReport.INT_FORMAT.format(endFollowers[i] - startFollowers[i]))
                        .append("）\n");
            }

            preparedStatement.close();
            tddConnection.close();
        } catch (ParseException | SQLException | ClassNotFoundException exception) {
            exception.printStackTrace();
        }
    }

    /**
     * 本方法只会被getDeltaFansOfMembers方法调用
     *
     * @param preparedStatement 预编译
     * @param time              时间（日期）
     * @param mids              mid序列
     * @param followers         粉丝数序列（用来保存数据）
     * @throws SQLException 可能会抛出SQLException
     */
    private static void getFollowersByMids(PreparedStatement preparedStatement, int time, int[] mids, int[] followers) throws SQLException {
        for (int i = 0; i < mids.length; i++) {
            preparedStatement.setInt(1, time - 86400);
            preparedStatement.setInt(2, time + 86400);
            preparedStatement.setInt(3, mids[i]);
            preparedStatement.executeQuery();
            ResultSet resultSet = preparedStatement.getResultSet();
            while (resultSet.next()) {
                followers[i] = resultSet.getInt("follower");
            }
        }
    }
}
